﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;

namespace EntityMapperCreater.DataAccess
{
    /// <summary>
    /// sqlite数据库操作对象
    /// </summary>
    public partial class SqliteDatabaseService : IDatabaseService
    {
        private string connStr;
        private int timeout = 2000;
        private object conn;
        //private string dbPath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DBConfig.xml");

        public SqliteDatabaseService()
        {
            try
            {
                XmlDocument xda = new XmlDocument();
                xda.Load(InitStaticData.dbPath);
                //connStr = "Data Source={path};password=123456;";"
                connStr = "Data Source=" + xda.SelectSingleNode("DBConfig/SQLITE/database").InnerText;
                //connStr += ";password=" + xda.SelectSingleNode("DBConfig/SQLITE/password").InnerText;
            }
            catch (Exception exc)
            {
                throw new Exception("数据库配置文件DBConfig.xml不存在或格式不对！");
            }
        }

        /// <summary>
        /// 检查西软数据库是否连接成功
        /// </summary>
        /// <returns></returns>
        public bool checkDBconn()
        {
            try
            {
                DataTable result = new DataTable();
                using (SQLiteConnection connection = new SQLiteConnection(connStr))
                {
                    connection.Open();
                    SQLiteDataAdapter SQLiteDataAdapter = new SQLiteDataAdapter("select sqlite_version()", connection);
                    SQLiteDataAdapter.Fill(result);
                    SQLiteDataAdapter.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
                InitStaticData.dbVersion = result.Rows[0][0].ToString() ?? "3.0.3";

                return true;
            }
            catch { return false; }
        }

        #region 基础SQL指令
        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public DataTable Select(string sqlStr)
        {
            DataTable result = new DataTable();
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(connStr))
                {
                    connection.Open();
                    SQLiteDataAdapter SQLiteDataAdapter = new SQLiteDataAdapter(sqlStr, connection);
                    SQLiteDataAdapter.Fill(result);
                    SQLiteDataAdapter.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            catch (SQLiteException ex)
            {
                throw new Exception(ex.Message.ToString(), ex);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return result;
        }
        /// <summary>
        /// 更新数据表
        /// </summary>
        /// <param name="sqlstr"></param>
        public void Update(string sqlstr)
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(connStr))
                {
                    connection.Open();
                    SQLiteCommand command = new SQLiteCommand(sqlstr, connection);
                    connection.Open();
                    command.ExecuteNonQuery();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            catch (SQLiteException ex)
            {
                throw new Exception(ex.Message.ToString(), ex);
            }
            catch (Exception exc)
            {
                throw exc;
            }
        }

        /// <summary>
        /// 查询数据库,返回string
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public string Select_str(string sqlStr)
        {
            DataTable result = new DataTable();
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(connStr))
                {
                    connection.Open();
                    SQLiteDataAdapter SQLiteDataAdapter = new SQLiteDataAdapter(sqlStr, connection);
                    SQLiteDataAdapter.Fill(result);
                    SQLiteDataAdapter.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            catch (SQLiteException ex)
            {
                throw new Exception(ex.Message.ToString(), ex);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            if (result.Rows.Count > 0)
                return result.Rows[0][0].ToString().Trim();
            else
                return "";
        }

        public bool Exists(string sqlStr)
        {
            bool lb_result = false;
            DataTable result = new DataTable();
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(connStr))
                {
                    connection.Open();
                    SQLiteDataAdapter SQLiteDataAdapter = new SQLiteDataAdapter(sqlStr, connection);
                    SQLiteDataAdapter.Fill(result);
                    SQLiteDataAdapter.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
                if (result.Rows.Count > 0)
                    lb_result = true;
            }
            catch (SQLiteException ex)
            {
                throw new Exception(ex.Message.ToString(), ex);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return lb_result;
        }

        public bool ExistsColumn(string tableName, string columnName)
        {
            bool lb_result = false;
            DataTable result = new DataTable();
            try
            {
                string sqlStr = "select 1 from sysobjects,syscolumns where sysobjects.id = syscolumns.id  and sysobjects.type='U'"
                    + " and sysobjects.name='" + tableName + "' and syscolumns.name='" + columnName + "'";
                using (SQLiteConnection connection = new SQLiteConnection(connStr))
                {
                    connection.Open();
                    SQLiteDataAdapter SQLiteDataAdapter = new SQLiteDataAdapter(sqlStr, connection);
                    SQLiteDataAdapter.Fill(result);
                    SQLiteDataAdapter.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
                if (result.Rows.Count > 0)
                    lb_result = true;
            }
            catch (SQLiteException ex)
            {
                throw new Exception(ex.Message.ToString(), ex);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return lb_result;
        }
        #endregion
        
        #region 参数化Select
        public DataTable Select(string sqlStr, IDbDataParameter[] inputParams)
        {
            DataTable result = new DataTable();
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(connStr))
                {
                    SQLiteCommand command = new SQLiteCommand();
                    command.Connection = connection;
                    command.CommandText = sqlStr;
                    if (inputParams != null)
                    {
                        foreach (var param in inputParams)
                        {
                            //param.Value = FOXHISCharset.insertToSybase125(param.Value.ToString());
                            command.Parameters.Add(param);
                        }
                    }
                    connection.Open();
                    SQLiteDataAdapter SQLiteDataAdapter = new SQLiteDataAdapter(command);
                    SQLiteDataAdapter.Fill(result);
                    SQLiteDataAdapter.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            catch (SQLiteException ex)
            {
                throw new Exception(ex.Message.ToString(), ex);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return result;
        }
        #endregion
    }
}
